﻿using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

namespace Voluntariar.Sistema.Connection
{
    abstract class Database
    {

        private static readonly string CONN_STRING = System.Configuration.ConfigurationManager.ConnectionStrings["StringConexao"].ConnectionString;

        private static SqlDataAdapter dataAdapter;

        public static int ExecuteNonQuery(string cmdText)
        {
            SqlCommand cmd = new SqlCommand(cmdText);
            SqlConnection conn = new SqlConnection(CONN_STRING);
            SqlTransaction trans;

            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();

                trans = conn.BeginTransaction();

                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = cmdText;

                if (trans != null)
                    cmd.Transaction = trans;

                int val = cmd.ExecuteNonQuery();

                cmd.Transaction.Commit();

                return val;
            }
            catch (SqlException ex)
            {
                cmd.Transaction.Rollback();
                throw (ex);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }

        }

        public static int ExecuteNonQuery(string cmdText, SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand(cmdText);
            SqlConnection conn = new SqlConnection(CONN_STRING);
            conn.Open();
            SqlTransaction trans = conn.BeginTransaction();

            try
            {
                PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();

                return val;
            }
            catch (SqlException ex)
            {
                cmd.Transaction.Rollback();
                conn.Close();
                conn.Dispose();
                throw (ex);
            }
            finally
            {
                if (conn != null)
                {
                    cmd.Transaction.Commit();
                    conn.Close();
                    conn.Dispose();
                }
            }

        }

        public static int ExecuteNonQuery(string cmdText, SqlParameter[] cmdParms, SqlTransaction trans)
        {
            SqlCommand cmd = new SqlCommand();
            try
            {
                PrepareCommand(cmd, trans.Connection, trans, cmdText, cmdParms);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
            catch (SqlException ex)
            {
                throw (ex);
            }
            finally
            {
                if (trans.Connection != null)
                {
                    trans.Connection.Close();
                    trans.Connection.Dispose();
                }
            }
        }

        public static object ExecuteScalar(string cmdText, SqlParameter[] cmdParms, bool isProcedure = true)
        {
            SqlCommand cmd = new SqlCommand(cmdText);
            SqlConnection conn = new SqlConnection(CONN_STRING);

            try
            {
                if (isProcedure)
                {
                    PrepareCommand(cmd, conn, null, cmdText, cmdParms);
                }
                object firstColumn = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                conn.Close();
                return firstColumn;
            }
            catch (SqlException ex)
            {
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }

                throw (ex);
            }

        }

        public static DataTable ExecuteDt(string cmdText)
        {
            SqlConnection conn = new SqlConnection(CONN_STRING);
            SqlDataAdapter da = new SqlDataAdapter();
            SqlCommand sqlCommand = new SqlCommand(cmdText);
            sqlCommand.Connection = conn;
            sqlCommand.Connection.Open();
            sqlCommand.Transaction = sqlCommand.Connection.BeginTransaction();
            da.SelectCommand = sqlCommand;


            DataTable dt = new DataTable();

            try
            {
                da.Fill(dt);
                da.Dispose();
                sqlCommand.Transaction.Commit();
                return dt;

            }
            catch (SqlException ex)
            {
                sqlCommand.Transaction.Rollback();
                sqlCommand.Connection.Close();
                sqlCommand.Connection.Dispose();
                throw (ex);
            }
            finally
            {
                if (conn != null)
                {
                    sqlCommand.Connection.Close();
                    sqlCommand.Connection.Dispose();
                }
            }
        }

        public static DataTable ExecuteDt(string conexao, string cmdText, params SqlParameter[] cmdParms)
        {

            DataTable dt = new DataTable();
            try
            {

                PrepararDataAdapter(CommandType.StoredProcedure, cmdText, cmdParms);
                dataAdapter.Fill(dt);


                return dt;
            }
            catch (SqlException ex)
            {
                throw (ex);
            }

        }

        public static DataTable ExecuteDt(string cmdText, params SqlParameter[] cmdParms)
        {
            return ExecuteDt(CONN_STRING, cmdText, cmdParms);
        }

        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = cmdText;

            if (trans != null)
                cmd.Transaction = trans;

            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                {
                    cmd.Parameters.Add(parm);
                }

            }
        }

        private static void PrepararDataAdapter(CommandType tipoComando, string cmdTexto, IDataParameter[] cmdParametros)
        {
            dataAdapter = new SqlDataAdapter();
            dataAdapter.SelectCommand = new SqlCommand();
            dataAdapter.SelectCommand.Connection = new SqlConnection();
            dataAdapter.SelectCommand.Connection.ConnectionString = CONN_STRING;
            dataAdapter.SelectCommand.CommandText = cmdTexto;
            dataAdapter.SelectCommand.CommandType = tipoComando;
            if (cmdParametros != null)
            {
                foreach (IDataParameter parametros in cmdParametros)
                    dataAdapter.SelectCommand.Parameters.Add(parametros);
            }
        }

        public static SqlDataReader ExecuteReader(string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand(cmdText);
            SqlConnection conn = new SqlConnection(CONN_STRING);

            try
            {
                PrepareCommand(cmd, conn, null, cmdText, cmdParms);
                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch (SqlException ex)
            {
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }

                throw (ex);
            }

        }

    }
}